IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_GetUsersForManagement]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_GetUsersForManagement]
GO
create procedure [dbo].[cms_sp_GetUsersForManagement]
@SiteGroupId int,
@SiteId int,
@CurrentPage int,
@PageCapacity int,
@SortField nvarchar(max) = 'UserId',
@SortDirection nvarchar(max) = 'Ascending'
as
declare @resultCount int
declare @result table
(
UserId int not null,
SiteGroupId int null,
SiteId int null,
LoginName [nvarchar](150),
UserEmail [nvarchar](250),
Firstname [nvarchar](250) null,
Lastname [nvarchar](255) null,
IsEnabled bit not null,
IsSystem bit not null,
CreatedBy int not null,
DateCreated datetime not null,
LastUpdatedBy int null,
DateLastUpdated datetime null,
UserCreatedBy nvarchar(250) null,
UserLastUpdatedBy nvarchar(250) null,
SortOrderAsc int,
SortOrderDesc int
)
insert into @result
(
UserId,
SiteId,
SiteGroupId,
LoginName,
UserEmail,
Firstname,
Lastname,
IsEnabled,
IsSystem,
CreatedBy,
DateCreated,
LastUpdatedBy,
DateLastUpdated,
UserCreatedBy,
UserLastUpdatedBy,
SortOrderAsc,
SortOrderDesc
)
select 
	Users.UserId,
	Users.SiteId,
	Users.SiteGroupId,
	Users.LoginName,
	Users.UserEmail,
	Users.Firstname,
	Users.Lastname,
	Users.IsEnabled,
	Users.IsSystem,
	Users.CreatedBy,
	Users.DateCreated,
	Users.LastUpdatedBy,
	Users.DateLastUpdated,
	Creators.Firstname + ' ' + Creators.Lastname as UserCreatedBy,
	Updaters.Firstname + ' ' + Updaters.Lastname as UserLastUpdatedBy,
	case @SortField
	when 'UserName' then row_number() over(order by Users.UserName)
	when 'LoginName' then row_number() over(order by Users.LoginName)
	when 'IsEnabled' then row_number() over(order by Users.IsEnabled)
	when 'IsSystem' then row_number() over(order by Users.IsSystem)
	when 'DateCreated' then row_number() over(order by Users.DateCreated)
	when 'UserCreatedBy' then row_number() over(order by Creators.UserName)
	when 'Id' then row_number() over(order by Users.UserId)			
	else row_number() over(order by Users.UserId)
	end,	
	case @SortField
	when 'UserName' then row_number() over(order by Users.UserName desc)
	when 'LoginName' then row_number() over(order by Users.LoginName desc)
	when 'IsEnabled' then row_number() over(order by Users.IsEnabled desc)
	when 'IsSystem' then row_number() over(order by Users.IsSystem desc)
	when 'DateCreated' then row_number() over(order by Users.DateCreated desc)
	when 'UserCreatedBy' then row_number() over(order by Creators.UserName desc)
	when 'Id' then row_number() over(order by Users.UserId desc)			
	else row_number() over(order by Users.UserId desc)
	end	
from [dbo].[Users]
left join [dbo].[Users] as Creators
	on Creators.UserId = Users.CreatedBy
left join [dbo].[Users] as Updaters
	on Updaters.UserId = Users.LastUpdatedBy
where (Users.SiteGroupId is null and Users.SiteId is null) or
	(Users.SiteGroupId = @SiteGroupId and Users.SiteId is null) or
	(Users.SiteGroupId = @SiteGroupId and Users.SiteId = @SiteId)
select @resultCount = COUNT(UserId) from @result
if(@resultCount < @PageCapacity)
begin
	set @PageCapacity = @resultCount
end
select 
	@resultCount as TotalCount, 
	UserId,
	SiteId,
	SiteGroupId,
	LoginName,
	UserEmail,
	Firstname,
	Lastname,
	IsEnabled,
	IsSystem,
	CreatedBy,
	DateCreated,
	LastUpdatedBy,
	DateLastUpdated,
	UserCreatedBy,
	UserLastUpdatedBy
from
 @result as seq
where ((@SortDirection='Ascending' or @SortDirection is null) and seq.SortOrderAsc between @PageCapacity * (@CurrentPage - 1) + 1 and @PageCapacity * @CurrentPage)
or (@SortDirection='Descending' and seq.SortOrderDesc between @PageCapacity * (@CurrentPage - 1) + 1 and @PageCapacity * @CurrentPage)
order by 
case @SortDirection
when 'Descending' then SortOrderDesc
else SortOrderAsc
end

GO

